package model

import (
	"database/sql"
	"fmt"
	"strings"
	"time"

	log "github.com/sirupsen/logrus"
)

// StockDataQT 结构体表示股票数据表的数据模型
type StockDataQT struct {
	ID                     int64     `json:"id"`                         // 数据库中的主键ID
	StockCode              string    `json:"stock_code"`                 // 股票代码
	StockName              string    `json:"stock_name"`                 // 股票名称
	CurrentPrice           float64   `json:"current_price"`              // 当前价格
	YesterdayClosePrice    float64   `json:"yesterday_close_price"`      // 昨收价
	TodayOpenPrice         float64   `json:"today_open_price"`           // 今开价
	Volume                 int       `json:"volume"`                     // 成交量（手）
	OuterDisk              int       `json:"outer_disk"`                 // 外盘
	InnerDisk              int       `json:"inner_disk"`                 // 内盘
	BuyOnePrice            float64   `json:"buy_one_price"`              // 买一价
	BuyOneVolume           int       `json:"buy_one_volume"`             // 买一量
	BuyTwoPrice            float64   `json:"buy_two_price"`              // 买二价
	BuyTwoVolume           int       `json:"buy_two_volume"`             // 买二量
	BuyThreePrice          float64   `json:"buy_three_price"`            // 买三价
	BuyThreeVolume         int       `json:"buy_three_volume"`           // 买三量
	BuyFourPrice           float64   `json:"buy_four_price"`             // 买四价
	BuyFourVolume          int       `json:"buy_four_volume"`            // 买四量
	BuyFivePrice           float64   `json:"buy_five_price"`             // 买五价
	BuyFiveVolume          int       `json:"buy_five_volume"`            // 买五量
	SellOnePrice           float64   `json:"sell_one_price"`             // 卖一价
	SellOneVolume          int       `json:"sell_one_volume"`            // 卖一量
	SellTwoPrice           float64   `json:"sell_two_price"`             // 卖二价
	SellTwoVolume          int       `json:"sell_two_volume"`            // 卖二量
	SellThreePrice         float64   `json:"sell_three_price"`           // 卖三价
	SellThreeVolume        int       `json:"sell_three_volume"`          // 卖三量
	SellFourPrice          float64   `json:"sell_four_price"`            // 卖四价
	SellFourVolume         int       `json:"sell_four_volume"`           // 卖四量
	SellFivePrice          float64   `json:"sell_five_price"`            // 卖五价
	SellFiveVolume         int       `json:"sell_five_volume"`           // 卖五量
	LatestTransaction      string    `json:"latest_transaction"`         // 最近逐笔成交
	TransactionTime        string    `json:"transaction_time"`           // 时间
	ChangeAmount           float64   `json:"change_amount"`              // 涨跌额
	ChangePercent          float64   `json:"change_percent"`             // 涨跌幅
	HighestPrice           float64   `json:"highest_price"`              // 最高价
	LowestPrice            float64   `json:"lowest_price"`               // 最低价
	BuySellPriceVolumeBuy  float64   `json:"buy_sell_price_volume_buy"`  // 价格/成交量（手）（买入）
	BuySellPriceVolumeSell float64   `json:"buy_sell_price_volume_sell"` // 价格/成交量（手）（卖出）
	TurnoverAmount         float64   `json:"turnover_amount"`            // 成交额（万）
	TurnoverVolume         int       `json:"turnover_volume"`            // 成交量
	PERatio                float64   `json:"pe_ratio"`                   // 市盈率
	Capitalization         float64   `json:"capitalization"`             // 股本（亿）
	TotalMarketValue       float64   `json:"total_market_value"`         // 总市值（亿）
	CirculatingMarketValue float64   `json:"circulating_market_value"`   // 流通市值（亿）
	Amplitude              float64   `json:"amplitude"`                  // 振幅
	TurnoverRate           float64   `json:"turnover_rate"`              // 换手率
	PBRatio                float64   `json:"pb_ratio"`                   // 市净率
	CommissionRatio        float64   `json:"commission_ratio"`           // 委比
	VolumeRatio            float64   `json:"volume_ratio"`               // 量比
	Date                   string    `json:"date"`                       // 日期字段
	CreatedAt              time.Time `json:"created_at"`                 // 创建时间
	UpdatedAt              time.Time `json:"updated_at"`                 // 更新时间
}

// 连续涨停多少天
func GetFiter(query string) ([]string, error) {
	// log.Info(query)
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stocks []string
	for rows.Next() {
		var stock_code string // 数据库中的时间字段以 []uint8 类型表示
		if err := rows.Scan(&stock_code); err != nil {
			return nil, err
		}
		stocks = append(stocks, stock_code)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return stocks, nil
}

func GetStockDetail(page int, pageSize int, orderBy string, orderDirection string, stockCode string) ([]StockDataQT, error) {
	condition := ""
	// Validate parameters
	if page < 1 {
		page = 1
	}
	if pageSize < 1 {
		pageSize = 10
	}
	if orderDirection != "ASC" && orderDirection != "DESC" && orderDirection != "asc" && orderDirection != "desc" {
		orderDirection = "ASC"
	}
	// Construct the SQL query
	offset := (page - 1) * pageSize
	query := fmt.Sprintf(
		`SELECT id, stock_code, stock_name, current_price, yesterday_close_price, today_open_price, volume,
                outer_disk, inner_disk, buy_one_price, buy_one_volume, buy_two_price, buy_two_volume,
                buy_three_price, buy_three_volume, buy_four_price, buy_four_volume, buy_five_price,
                buy_five_volume, sell_one_price, sell_one_volume, sell_two_price, sell_two_volume,
                sell_three_price, sell_three_volume, sell_four_price, sell_four_volume, sell_five_price,
                sell_five_volume, latest_transaction, transaction_time, change_amount, change_percent,
                highest_price, lowest_price, buy_sell_price_volume_buy, buy_sell_price_volume_sell,
                turnover_amount, turnover_volume, pe_ratio, capitalization, total_market_value,
                circulating_market_value, amplitude, turnover_rate, pb_ratio, commission_ratio, volume_ratio,
                date, created_at, updated_at 
         FROM stock_qt
		 WHERE stock_code not in (399300, 399106, 399903, 399001, 399006, 399005) 
		 AND stock_code = '%s' 
		 %s  
         ORDER BY %s %s
         LIMIT %d OFFSET %d`,
		stockCode, condition, orderBy, orderDirection, pageSize, offset,
	)

	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stocks []StockDataQT
	for rows.Next() {
		var stock StockDataQT
		var createdAt, updatedAt []uint8 // 数据库中的时间字段以 []uint8 类型表示

		if err := rows.Scan(
			&stock.ID, &stock.StockCode, &stock.StockName, &stock.CurrentPrice, &stock.YesterdayClosePrice,
			&stock.TodayOpenPrice, &stock.Volume, &stock.OuterDisk, &stock.InnerDisk, &stock.BuyOnePrice,
			&stock.BuyOneVolume, &stock.BuyTwoPrice, &stock.BuyTwoVolume, &stock.BuyThreePrice,
			&stock.BuyThreeVolume, &stock.BuyFourPrice, &stock.BuyFourVolume, &stock.BuyFivePrice,
			&stock.BuyFiveVolume, &stock.SellOnePrice, &stock.SellOneVolume, &stock.SellTwoPrice,
			&stock.SellTwoVolume, &stock.SellThreePrice, &stock.SellThreeVolume, &stock.SellFourPrice,
			&stock.SellFourVolume, &stock.SellFivePrice, &stock.SellFiveVolume, &stock.LatestTransaction,
			&stock.TransactionTime, &stock.ChangeAmount, &stock.ChangePercent, &stock.HighestPrice,
			&stock.LowestPrice, &stock.BuySellPriceVolumeBuy, &stock.BuySellPriceVolumeSell,
			&stock.TurnoverAmount, &stock.TurnoverVolume, &stock.PERatio, &stock.Capitalization,
			&stock.TotalMarketValue, &stock.CirculatingMarketValue, &stock.Amplitude, &stock.TurnoverRate,
			&stock.PBRatio, &stock.CommissionRatio, &stock.VolumeRatio, &stock.Date, &createdAt,
			&updatedAt,
		); err != nil {
			return nil, err
		}

		// 将 []uint8 类型的数据库时间字段转换为 time.Time 类型
		stock.CreatedAt, err = time.Parse("2006-01-02 15:04:05", string(createdAt))
		if err != nil {
			return nil, fmt.Errorf("error parsing created_at: %v", err)
		}

		stock.UpdatedAt, err = time.Parse("2006-01-02 15:04:05", string(updatedAt))
		if err != nil {
			return nil, fmt.Errorf("error parsing updated_at: %v", err)
		}

		stocks = append(stocks, stock)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return stocks, nil
}

func GetPaginatedStocks(page int, pageSize int, orderBy string, orderDirection string, stockCode []string, searchCode bool) ([]StockDataQT, error) {
	// Validate parameters
	if page < 1 {
		page = 1
	}
	if pageSize < 1 {
		pageSize = 10
	}
	if orderDirection != "ASC" && orderDirection != "DESC" && orderDirection != "asc" && orderDirection != "desc" {
		orderDirection = "ASC"
	}

	condition := ""

	if searchCode {
		if len(stockCode) == 0 {
			return []StockDataQT{}, nil
		}
		codeStr := strings.Join(stockCode, ", ")
		condition += fmt.Sprintf(" AND stock_code in (%s) ", codeStr)
	}

	// Construct the SQL query
	offset := (page - 1) * pageSize
	query := fmt.Sprintf(
		`SELECT id, stock_code, stock_name, current_price, yesterday_close_price, today_open_price, volume,
                outer_disk, inner_disk, buy_one_price, buy_one_volume, buy_two_price, buy_two_volume,
                buy_three_price, buy_three_volume, buy_four_price, buy_four_volume, buy_five_price,
                buy_five_volume, sell_one_price, sell_one_volume, sell_two_price, sell_two_volume,
                sell_three_price, sell_three_volume, sell_four_price, sell_four_volume, sell_five_price,
                sell_five_volume, latest_transaction, transaction_time, change_amount, change_percent,
                highest_price, lowest_price, buy_sell_price_volume_buy, buy_sell_price_volume_sell,
                turnover_amount, turnover_volume, pe_ratio, capitalization, total_market_value,
                circulating_market_value, amplitude, turnover_rate, pb_ratio, commission_ratio, volume_ratio,
                date, created_at, updated_at 
         FROM stock_qt
		 WHERE stock_code not in (399300, 399106, 399903, 399001, 399006, 399005) 
		 AND date = (SELECT date FROM (SELECT date FROM stock_qt GROUP BY date ORDER BY date DESC LIMIT 1 ) AS subquery ORDER BY date ASC LIMIT 1) 
		 %s 
         ORDER BY %s %s
         LIMIT %d OFFSET %d`,
		condition, orderBy, orderDirection, pageSize, offset,
	)

	// log.Println(query)

	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var stocks []StockDataQT
	for rows.Next() {
		var stock StockDataQT
		var createdAt, updatedAt []uint8 // 数据库中的时间字段以 []uint8 类型表示

		if err := rows.Scan(
			&stock.ID, &stock.StockCode, &stock.StockName, &stock.CurrentPrice, &stock.YesterdayClosePrice,
			&stock.TodayOpenPrice, &stock.Volume, &stock.OuterDisk, &stock.InnerDisk, &stock.BuyOnePrice,
			&stock.BuyOneVolume, &stock.BuyTwoPrice, &stock.BuyTwoVolume, &stock.BuyThreePrice,
			&stock.BuyThreeVolume, &stock.BuyFourPrice, &stock.BuyFourVolume, &stock.BuyFivePrice,
			&stock.BuyFiveVolume, &stock.SellOnePrice, &stock.SellOneVolume, &stock.SellTwoPrice,
			&stock.SellTwoVolume, &stock.SellThreePrice, &stock.SellThreeVolume, &stock.SellFourPrice,
			&stock.SellFourVolume, &stock.SellFivePrice, &stock.SellFiveVolume, &stock.LatestTransaction,
			&stock.TransactionTime, &stock.ChangeAmount, &stock.ChangePercent, &stock.HighestPrice,
			&stock.LowestPrice, &stock.BuySellPriceVolumeBuy, &stock.BuySellPriceVolumeSell,
			&stock.TurnoverAmount, &stock.TurnoverVolume, &stock.PERatio, &stock.Capitalization,
			&stock.TotalMarketValue, &stock.CirculatingMarketValue, &stock.Amplitude, &stock.TurnoverRate,
			&stock.PBRatio, &stock.CommissionRatio, &stock.VolumeRatio, &stock.Date, &createdAt,
			&updatedAt,
		); err != nil {
			return nil, err
		}

		// 将 []uint8 类型的数据库时间字段转换为 time.Time 类型
		stock.CreatedAt, err = time.Parse("2006-01-02 15:04:05", string(createdAt))
		if err != nil {
			return nil, fmt.Errorf("error parsing created_at: %v", err)
		}

		stock.UpdatedAt, err = time.Parse("2006-01-02 15:04:05", string(updatedAt))
		if err != nil {
			return nil, fmt.Errorf("error parsing updated_at: %v", err)
		}

		stocks = append(stocks, stock)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return stocks, nil
}

// SaveStockDataQT 将股票数据保存到数据库，根据唯一索引更新或插入数据
func SaveStockDataQT(stockData StockDataQT) error {
	// 获取当前日期
	stockData.Date = time.Now().Format("2006-01-02") // 格式化为 "年-月-日"
	// stockData.Date = "2024-09-14"

	// 查询数据库中是否已存在相同 stock_code 和 date 的记录
	var count int
	err := db.QueryRow("SELECT COUNT(*) FROM stock_qt WHERE stock_code = ? AND date = ?", stockData.StockCode, stockData.Date).Scan(&count)
	if err != nil {
		return fmt.Errorf("failed to query database: %v", err)
	}

	var stmt *sql.Stmt
	if count > 0 {
		log.Println("数据已存在")

		// 已存在记录，执行更新操作
		stmt, err = db.Prepare(`
			UPDATE stock_qt SET 
				stock_name=?, current_price=?, yesterday_close_price=?, today_open_price=?, volume=?, 
				outer_disk=?, inner_disk=?, buy_one_price=?, buy_one_volume=?, buy_two_price=?, 
				buy_two_volume=?, buy_three_price=?, buy_three_volume=?, buy_four_price=?, 
				buy_four_volume=?, buy_five_price=?, buy_five_volume=?, sell_one_price=?, 
				sell_one_volume=?, sell_two_price=?, sell_two_volume=?, sell_three_price=?, 
				sell_three_volume=?, sell_four_price=?, sell_four_volume=?, sell_five_price=?, 
				sell_five_volume=?, latest_transaction=?, transaction_time=?, change_amount=?, 
				change_percent=?, highest_price=?, lowest_price=?, buy_sell_price_volume_buy=?, 
				buy_sell_price_volume_sell=?, turnover_amount=?, turnover_volume=?, pe_ratio=?, 
				capitalization=?, total_market_value=?, circulating_market_value=?, amplitude=?, 
				turnover_rate=?, pb_ratio=?, commission_ratio=?, volume_ratio=?, updated_at=NOW() 
			WHERE stock_code=? AND date=?
		`)
		if err != nil {
			return fmt.Errorf("failed to prepare update statement: %v", err)
		}
		defer stmt.Close()

		// 执行更新操作
		_, err = stmt.Exec(
			stockData.StockName, stockData.CurrentPrice, stockData.YesterdayClosePrice, stockData.TodayOpenPrice,
			stockData.Volume, stockData.OuterDisk, stockData.InnerDisk, stockData.BuyOnePrice, stockData.BuyOneVolume,
			stockData.BuyTwoPrice, stockData.BuyTwoVolume, stockData.BuyThreePrice, stockData.BuyThreeVolume,
			stockData.BuyFourPrice, stockData.BuyFourVolume, stockData.BuyFivePrice, stockData.BuyFiveVolume,
			stockData.SellOnePrice, stockData.SellOneVolume, stockData.SellTwoPrice, stockData.SellTwoVolume,
			stockData.SellThreePrice, stockData.SellThreeVolume, stockData.SellFourPrice, stockData.SellFourVolume,
			stockData.SellFivePrice, stockData.SellFiveVolume, stockData.LatestTransaction, stockData.TransactionTime,
			stockData.ChangeAmount, stockData.ChangePercent, stockData.HighestPrice, stockData.LowestPrice,
			stockData.BuySellPriceVolumeBuy, stockData.BuySellPriceVolumeSell, stockData.TurnoverAmount,
			stockData.TurnoverVolume, stockData.PERatio, stockData.Capitalization, stockData.TotalMarketValue,
			stockData.CirculatingMarketValue, stockData.Amplitude, stockData.TurnoverRate, stockData.PBRatio,
			stockData.CommissionRatio, stockData.VolumeRatio, stockData.StockCode, stockData.Date,
		)
		if err != nil {
			return fmt.Errorf("failed to execute update query: %v", err)
		}
		log.Println("数据更新成功")
	} else {
		// 不存在记录，执行插入操作
		stmt, err = db.Prepare(`
			INSERT INTO stock_qt (
				stock_code, stock_name, current_price, yesterday_close_price, today_open_price, volume,
				outer_disk, inner_disk, buy_one_price, buy_one_volume, buy_two_price, buy_two_volume,
				buy_three_price, buy_three_volume, buy_four_price, buy_four_volume, buy_five_price,
				buy_five_volume, sell_one_price, sell_one_volume, sell_two_price, sell_two_volume,
				sell_three_price, sell_three_volume, sell_four_price, sell_four_volume, sell_five_price,
				sell_five_volume, latest_transaction, transaction_time, change_amount, change_percent,
				highest_price, lowest_price, buy_sell_price_volume_buy, buy_sell_price_volume_sell,
				turnover_amount, turnover_volume, pe_ratio, capitalization, total_market_value,
				circulating_market_value, amplitude, turnover_rate, pb_ratio, commission_ratio,
				volume_ratio, date, created_at, updated_at
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
		`)
		if err != nil {
			return fmt.Errorf("failed to prepare insert statement: %v", err)
		}
		defer stmt.Close()

		// 执行插入操作
		_, err = stmt.Exec(
			stockData.StockCode, stockData.StockName, stockData.CurrentPrice, stockData.YesterdayClosePrice,
			stockData.TodayOpenPrice, stockData.Volume, stockData.OuterDisk, stockData.InnerDisk, stockData.BuyOnePrice,
			stockData.BuyOneVolume, stockData.BuyTwoPrice, stockData.BuyTwoVolume, stockData.BuyThreePrice,
			stockData.BuyThreeVolume, stockData.BuyFourPrice, stockData.BuyFourVolume, stockData.BuyFivePrice,
			stockData.BuyFiveVolume, stockData.SellOnePrice, stockData.SellOneVolume, stockData.SellTwoPrice,
			stockData.SellTwoVolume, stockData.SellThreePrice, stockData.SellThreeVolume, stockData.SellFourPrice,
			stockData.SellFourVolume, stockData.SellFivePrice, stockData.SellFiveVolume, stockData.LatestTransaction,
			stockData.TransactionTime, stockData.ChangeAmount, stockData.ChangePercent, stockData.HighestPrice,
			stockData.LowestPrice, stockData.BuySellPriceVolumeBuy, stockData.BuySellPriceVolumeSell, stockData.TurnoverAmount,
			stockData.TurnoverVolume, stockData.PERatio, stockData.Capitalization, stockData.TotalMarketValue,
			stockData.CirculatingMarketValue, stockData.Amplitude, stockData.TurnoverRate, stockData.PBRatio,
			stockData.CommissionRatio, stockData.VolumeRatio, stockData.Date,
		)

		if err != nil {
			return fmt.Errorf("failed to execute insert query: %v", err)
		}
		log.Println("数据插入成功")
	}

	return nil
}

// BatchInsertStockDataQT inserts multiple StockDataQT records into the database in bulk
func BatchInsertStockDataQT(stockDataList []StockDataQT) error {
	// Prepare the SQL statement for batch insert
	stmt, err := db.Prepare(`
        INSERT INTO stock_qt (
            stock_code, stock_name, current_price, yesterday_close_price, today_open_price, volume,
            outer_disk, inner_disk, buy_one_price, buy_one_volume, buy_two_price, buy_two_volume,
            buy_three_price, buy_three_volume, buy_four_price, buy_four_volume, buy_five_price,
            buy_five_volume, sell_one_price, sell_one_volume, sell_two_price, sell_two_volume,
            sell_three_price, sell_three_volume, sell_four_price, sell_four_volume, sell_five_price,
            sell_five_volume, latest_transaction, transaction_time, change_amount, change_percent,
            highest_price, lowest_price, buy_sell_price_volume_buy, buy_sell_price_volume_sell,
            turnover_amount, turnover_volume, pe_ratio, capitalization, total_market_value,
            circulating_market_value, amplitude, turnover_rate, pb_ratio, commission_ratio,
            volume_ratio, date, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
    `)
	if err != nil {
		return fmt.Errorf("failed to prepare batch insert statement: %v", err)
	}
	defer stmt.Close()

	// Begin transaction
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %v", err)
	}

	// Iterate through each StockDataQT and execute the insert
	for _, stockData := range stockDataList {
		_, err = stmt.Exec(
			stockData.StockCode, stockData.StockName, stockData.CurrentPrice, stockData.YesterdayClosePrice,
			stockData.TodayOpenPrice, stockData.Volume, stockData.OuterDisk, stockData.InnerDisk, stockData.BuyOnePrice,
			stockData.BuyOneVolume, stockData.BuyTwoPrice, stockData.BuyTwoVolume, stockData.BuyThreePrice,
			stockData.BuyThreeVolume, stockData.BuyFourPrice, stockData.BuyFourVolume, stockData.BuyFivePrice,
			stockData.BuyFiveVolume, stockData.SellOnePrice, stockData.SellOneVolume, stockData.SellTwoPrice,
			stockData.SellTwoVolume, stockData.SellThreePrice, stockData.SellThreeVolume, stockData.SellFourPrice,
			stockData.SellFourVolume, stockData.SellFivePrice, stockData.SellFiveVolume, stockData.LatestTransaction,
			stockData.TransactionTime, stockData.ChangeAmount, stockData.ChangePercent, stockData.HighestPrice,
			stockData.LowestPrice, stockData.BuySellPriceVolumeBuy, stockData.BuySellPriceVolumeSell, stockData.TurnoverAmount,
			stockData.TurnoverVolume, stockData.PERatio, stockData.Capitalization, stockData.TotalMarketValue,
			stockData.CirculatingMarketValue, stockData.Amplitude, stockData.TurnoverRate, stockData.PBRatio,
			stockData.CommissionRatio, stockData.VolumeRatio, stockData.Date,
		)
		if err != nil {
			tx.Rollback()
			return fmt.Errorf("failed to execute batch insert query: %v", err)
		}
	}

	// Commit transaction
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %v", err)
	}

	log.Printf("Batch insertion of %d records successful", len(stockDataList))
	return nil
}
